﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ModelLayer.Login;
using System.Data;
using DataAccess;

namespace DataAccessLayer.Login
{
    public class LoginDataAccess
    {
        User user = User.Instance;

        public bool RISAutentification(string name, string enteredPwdHash)
        {
            GestorBD.Query = "SELECT COUNT(*) FROM User_ U,Staff_ S WHERE U.UserName_='" + name + "' and U.PasswordSaltedHash_='" + enteredPwdHash + "'and S.Type_='PRAD' and U.UserName_=S.UserName_";

            DataTable data = GestorBD.EjecutarSentencia();

            if (int.Parse(data.Rows[0].ItemArray[0].ToString()) == 1)

                return true;
            else
                return false;
        }


        public User GetUser(string userName)
        {

            GestorBD.Query = "SELECT U.OID_,U.UserName_,U.PasswordSalt_,U.PasswordSaltedHash_,U.LastLoginTime_,S.OID_,S.GivenName_,S.FamilyName_ FROM [RIS].[dbo].[User_] U,[RIS].[dbo].[Staff_] S where U.UserName_='" + userName + "' and S.UserName_='" + userName + " ' ";
            DataTable res = GestorBD.EjecutarSentencia();
            DataTable responsabilities;
            if (res.Rows.Count > 0)
            {
                responsabilities = new DataTable();
                user.setOid(Guid.Parse(res.Rows[0].ItemArray[0].ToString()));
                string name = res.Rows[0].ItemArray[1].ToString();
                user.setName(res.Rows[0].ItemArray[1].ToString());
                user.setSalt(res.Rows[0].ItemArray[2].ToString());
                user.setGivenName(res.Rows[0].ItemArray[6].ToString());
                user.setFamilyName(res.Rows[0].ItemArray[7].ToString());

                user.setPwdHash(res.Rows[0].ItemArray[3].ToString());
                if (res.Rows[0].ItemArray[4].ToString() != "")
                    user.setLastDate(DateTime.Parse(res.Rows[0].ItemArray[4].ToString()));
                //Llenar autorithy group
                responsabilities = SelectAutorithyGroups(name);

                for (int i = 0; i < responsabilities.Rows.Count; i++)
                {
                    DataRow row = responsabilities.Rows[i];
                    user.AddAutorithyGroup((Guid)row["OID"], row["NAME"].ToString());
                }

                if (res.Rows[0].ItemArray[5].ToString() != "")
                    user.setStaffOID(Guid.Parse(res.Rows[0].ItemArray[5].ToString()));
            }
            return user;
        }

        private DataTable SelectAutorithyGroups(string userName)
        {
            GestorBD.Query = "select AGU.AuthorityGroupOID_ AS OID,AG.Name_ AS NAME FROM User_ U,AuthorityGroupUser_ AGU ,AuthorityGroup_ AG WHERE U.OID_= AGU.UserOID_ AND AGU.AuthorityGroupOID_=AG.OID_ AND U.UserName_='" + userName + "'";
            return GestorBD.EjecutarSentencia();
        }



        public static void UpdateLastTimeEnter(string name)
        {
            //2011-01-22 13:19:05.000
            DateTime d = new DateTime();
            d = System.DateTime.Now;
            string shortDate = System.DateTime.Now.ToShortDateString();
            string militaryHour = d.ToString("HH:mm:ss");

            GestorBD.Query = "UPDATE User_ SET LastLoginTime_ =   CONVERT(datetime, '" + shortDate + " " + militaryHour + "',103)   WHERE UserName_='" + name + "'";

            GestorBD.Actualizacion();
        }



        public string ReturnSaltPwd(string userName)
        {
            GestorBD.Query = "SELECT PasswordSalt_  FROM User_ where UserName_='" + userName + "'";
            DataTable res = GestorBD.EjecutarSentencia();
            if (res.Rows.Count == 0)
                return "";
            else
                return res.Rows[0].ItemArray[0].ToString();
        }

        
    }
}

